import pymysql
from db import config

class Model:
	table_name = None # 表名
	link = None # 数据库连接对象
	cursor = None # 数据库游标对象
	pk = None	 # 表的主键名
	fields = []	# 表的所有字段名
	def __init__(self,table,config=config):
		try:
			self.table_name = table
			self.link = pymysql.connect(host=config.HOST,user=config.USER,password=config.PASSWD,port=config.PORT,db=config.DBNAME,charset="utf8")
			self.cursor = self.link.cursor(pymysql.cursors.DictCursor) # 以字典形式返回结果集
			#self.cursor = self.link.cursor() # 以元组形式返回结果集
			self.__loadfield()
		except Exception as err:
			print("数据操作对象初始化失败，原因%s"%err)

	def find(self,id):
		''' 查询指定主键的数据 '''
		sql = "select * from %s where %s=%s"%(self.table_name,self.pk,id)
		try:
			print(sql)
			self.cursor.execute(sql)
			return self.cursor.fetchall()
		except Exception as err:
			print("SQL查询执行错误,原因：%s"%err)
			return []

	def findall(self):
		''' 查询所有表数据 '''
		sql = "select * from %s" % self.table_name
		try:
			self.cursor.execute(sql)
			return self.cursor.fetchall()
		except Exception as err:
			print("SQL查询执行错误,原因：%s"%err)
			return []

	def select(self,where=[],order=None,limit=None):
		''' 条件查询 '''
		sql = "select * from %s"%(self.table_name)
		try:
			# 对传入参数做校验
			if isinstance(where,list) and len(where) > 0:
				sql += " where "+" and ".join(where)
			if order is not None:
				sql += " order by " + order
			if limit is not None:
				sql += " limit " + str(limit)
			self.cursor.execute(sql)
			return self.cursor.fetchall()
		except Exception as err:
			print("SQL查询执行错误,原因：%s"%err)
			return []

	def save(self,data=[]):
		''' 添加数据,支持同时添加多条'''
		sql = "insert into %s values" % self.table_name
		try:
			if isinstance(data,list) and len(data) > 0:
				for i in data:
					sql += "("+",".join(i)+"),"
				if sql[-1] == ",":
					sql = sql[:len(sql)-1]
				print(sql)
				self.cursor.execute(sql)
				self.link.commit()
				return self.cursor.lastrowid
		except Exception as err:
			print("SQL数据插入失败，原因：%s" % err)
			return 0

	def update(self,data={}):
		try:
			values=[]
			where=[]
			for k,v in data.items():
				#if (k in self.fields) and (k != self.pk):
				if k == "name":
					where.append("%s='%s'"%(k,v))
				else:
					values.append("%s='%s'"%(k,v))
			print(values)
			# 拼装sql
			sql = "update %s set %s where %s" % (self.table_name,",".join(values),",".join(where))
			#print(sql)
			self.cursor.execute(sql)
			self.link.commit()
			return self.cursor.rowcount
		except Exception as err:
			print("SQL数据修改失败，原因：%s" % err)
			return 0

	def delete(self,id):
		try:
			sql = "delete from %s where %s='%s'" % (self.table_name,self.pk,id)
			self.cursor.execute(sql)
			self.link.commit()
			return self.cursor.rowcount
		except Exception as err:
			print("SQL删除执行错误,原因：%s" % err)
			return 0

	def __loadfield(self):
		sql = "show columns from %s" % self.table_name
		self.cursor.execute(sql)
		fields = self.cursor.fetchall()
		for f in fields:
			self.fields.append(f['Field'])
			if f['Key'] == 'PRI':
				self.pk = f['Field'] 

	def __del__(self):
		if self.cursor != None:
			self.cursor.close()
		if self.link != None:
			self.link.close()
